﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Simple.Services.System.QHSEViolationCategory.Models;

public class DangerCategoryAnalysisService
{
    private readonly SimpleDbContext _context;
    private readonly CacheService _cacheService;
    private readonly QHSEDangerCategoryService _dangerCategoryService;
    private readonly QHSEDictionaryService _dictionaryService;
    private readonly QHSEViolationHSEService _HSEService;


    public DangerCategoryAnalysisService(SimpleDbContext context, QHSEDangerCategoryService dangerCategoryService,  QHSEDictionaryService dictionaryService,QHSEViolationHSEService hSEService, CacheService cacheService)
    {
        _context = context;
        _dangerCategoryService = dangerCategoryService;
        _dictionaryService = dictionaryService;
        _HSEService = hSEService;
        _cacheService = cacheService;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<List<DangerAnalysisResult>> DangerCategoryAnalysisAsync(DangerSpecialtyAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerCategoryDangerCategoryAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.DangerCategory != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerCategory = await _dangerCategoryService.GetSon(input.DangerCategory);
            var dangerCategoryChildren = new Dictionary<string, List<string>>();
            foreach (var category in dangerCategory)
            {
                var children = await _dangerCategoryService.GetChildren(category);
                dangerCategoryChildren[category] = children;
            }

            var dangerCounts = await query
                .GroupBy(d => d.DangerCategory)
                .Select(g => new
                {
                    DangerCategory = g.Key,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var category in dangerCategory)
            {
                var children = dangerCategoryChildren[category];
                var value = dangerCounts.Where(dc => children.Contains(dc.DangerCategory)).Sum(dc => dc.Count);
                result.Add(new DangerAnalysisResult { Name = category, Value = value });
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetDangerCategoryDangerCategoryAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<DangerAnalysisResult>> DangerTypeAnalysisAsync(DangerSpecialtyAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerCategoryDangerTypeAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.DangerCategory != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerType = await _dictionaryService.GetDictionaryAsync("1010");
            var dangerCounts = await query
                .GroupBy(d => d.DangerType)
                .Select(g => new
                {
                    DangerType = g.Key,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var type in dangerType)
            {
                var count = dangerCounts.FirstOrDefault(dc => dc.DangerType == type.Item)?.Count ?? 0;
                result.Add(new DangerAnalysisResult { Name = type.Item, Value = count });
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetDangerCategoryDangerTypeAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<DangerAnalysisResult>> DangerCauseAnalysisAsync(DangerSpecialtyAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerCategoryDangerCauseAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.DangerCategory != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var dangerCauses = await _dictionaryService.GetDictionaryAsync("1016");
            var dangerCounts = await query
                .GroupBy(d => d.CauseAnalysis)
                .Select(g => new
                {
                    CauseAnalysis = g.Key,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var dangerCause in dangerCauses)
            {
                var count = dangerCounts.FirstOrDefault(dc => dc.CauseAnalysis == dangerCause.Item)?.Count ?? 0;
                result.Add(new DangerAnalysisResult { Name = dangerCause.Item, Value = count });
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetDangerCategoryDangerCauseAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<DangerAnalysisResult>> DirectDepartmentAnalysisAsync(DangerSpecialtyAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerCategoryDirectDepartmentAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.DangerCategory != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var departments = await _dictionaryService.GetDictionaryAsync("1008");
            var dangerCounts = await query
                .GroupBy(d => d.DirectDepartment)
                .Select(g => new
                {
                    DirectDepartment = g.Key,
                    Count = g.Count()
                })
                .ToListAsync();

            foreach (var department in departments)
            {
                var newitem = department.Item.Contains("生产") ? "生产" : department.Item.Contains("人力资源") ? "人力资源（培训）" : "";
                var count = dangerCounts.Where(dc => dc.DirectDepartment == department.Item || dc.DirectDepartment == newitem).Sum(dc => dc.Count);
                result.Add(new DangerAnalysisResult { Name = department.Item, Value = count });
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetDangerCategoryDirectDepartmentAnalysisAsync(input, result);
        return result;
    }

    public async Task<List<DangerAnalysisResult>> HSEAnalysisAsync(DangerSpecialtyAnalysisInput input)
    {
        var cachedResult = await _cacheService.GetDangerCategoryHSEAnalysisAsync(input);
        if (cachedResult != null)
        {
            return cachedResult;
        }

        List<DangerAnalysisResult> result = new List<DangerAnalysisResult>();
        var query = _context.Set<QHSESysDanger>().AsNoTracking().AsQueryable();

        if (!string.IsNullOrEmpty(input.DangerOrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.DangerCategory != null)
        {
            var org = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.DangerOrganizationId);
            if (org == null) return result;

            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query.Where(u => input.CheckType.Contains(u.CheckType))
                         .Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty))
                         .Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                         .Join(_context.Set<QHSESysOrganization>(),
                               danger => danger.OrganizationId,
                               organization => organization.OrganizationId,
                               (danger, organization) => new { Danger = danger, Organization = organization })
                         .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                         .Select(joinResult => joinResult.Danger);

            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().FirstOrDefaultAsync(u => u.OrganizationId == input.CheckOrganizationId);
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query.Join(_context.Set<QHSESysOrganization>(),
                                       danger => danger.CheckOrganizationId,
                                       organization => organization.OrganizationId,
                                       (danger, organization) => new { Danger = danger, Organization = organization })
                                 .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                                 .Select(joinResult => joinResult.Danger);
                }
            }

            var hses = await _HSEService.GetAsync();
            if (hses != null)
            {
                var hseCounts = await query
                    .GroupBy(d => d.HseElement)
                    .Select(g => new
                    {
                        HseElement = g.Key,
                        Count = g.Count()
                    })
                    .ToListAsync();

                foreach (var hse in hses)
                {
                    var count = hseCounts.FirstOrDefault(h => h.HseElement.Equals(hse.Item))?.Count ?? 0;
                    result.Add(new DangerAnalysisResult { Name = hse.Item, Value = count });
                }
            }
        }

        result = result.OrderByDescending(result => result.Value).ToList();
        await _cacheService.SetDangerCategoryHSEAnalysisAsync(input, result);
        return result;
    }

}
